package com.jiazhong.basic.pack0518.dao;


import com.jiazhong.basic.pack0518.bean.Emp;
import com.jiazhong.basic.pack0518.db.DBManager;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class EmpDAO {

    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    // 版本1
    @SneakyThrows
    public void find1() {
        conn = DBManager.getConnection();
        String sql = "select * from emp where state=1";
        ps = conn.prepareStatement(sql);
        // 就是查询出来的结果集
        rs = ps.executeQuery();
        // 遍历
        while (rs.next()) { // 是否有下一行
            // 获取到下一行 rs
            // System.out.println(rs);
            // 获取到某一列
            int column1 = rs.getInt(1);
            String column2 = rs.getString(2);
            String column3 = rs.getString(3);
            int column4 = rs.getInt(4);
            String column5 = rs.getString(5);
            System.out.print("column1:" + column1 + ",");
            System.out.print("column2:" + column2 + ",");
            System.out.print("column3:" + column3 + ",");
            System.out.print("column4:" + column4 + ",");
            System.out.println("column5:" + column5 + ",");
        }
        rs.close();
        ps.close();
        conn.close();
    }

    // 下一个版本
    @SneakyThrows
    public void find2() {
        conn = DBManager.getConnection();
        String sql = "select empno as 'eno' from emp where state=1";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            // rs代表下一行
            // 可以通过虚拟列的名称，也可以通过索引号来获取到值
            int column1 = rs.getInt(1);
            int empno = rs.getInt("eno");
            System.out.println(column1 + "," + empno);
        }
    }

    // 全查询操作
    @SneakyThrows
    public List<Emp> find3() {
        List<Emp> list = new ArrayList<>();
        // 将数据库查询出来的"表格"转换成集合list
        // 查询出来"表格"
        conn = DBManager.getConnection();
        String sql = "select * from emp where state=1";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        // 转换
        while (rs.next()) {
            // rs ---> 行
            // 对象：模拟数据库emp表的一个类
            Emp emp = new Emp();
            // 将第一列的值赋予emp对象的empNo属性
            //int empNo = rs.getInt(1);
            emp.setEmpNo(rs.getInt(1));
            emp.setEname(rs.getString(2));
            emp.setJob(rs.getString("job"));
            emp.setMgr(rs.getInt(4));
            emp.setHireDate(rs.getString(5));
            emp.setSal(rs.getDouble(6));
            emp.setComm(rs.getDouble("comm"));
            emp.setDeptNo(rs.getInt(8));
            emp.setState(rs.getInt(9));
            // 将这个对象存放到集合中list
            list.add(emp);
        }
        return list;
    }

    // 按照主键ID查询操作
    @SneakyThrows
    public Emp findById(int empNo) {
        Emp emp = new Emp();
        conn = DBManager.getConnection();
        String sql = "select * from emp where state=1 and empno=?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, empNo);
        rs = ps.executeQuery(); // 最多一行数据
        if (rs.next()) {
            emp.setEmpNo(rs.getInt(1));
            emp.setEname(rs.getString(2));
            emp.setJob(rs.getString("job"));
            emp.setMgr(rs.getInt(4));
            emp.setHireDate(rs.getString(5));
            emp.setSal(rs.getDouble(6));
            emp.setComm(rs.getDouble("comm"));
            emp.setDeptNo(rs.getInt(8));
            emp.setState(rs.getInt(9));
        }
        return emp;
    }

    // 按照姓名模糊查询操作
    @SneakyThrows
    public List<Emp> findByEname(String ename) {
        List<Emp> list = new ArrayList<>();
        conn = DBManager.getConnection();
        String sql = "select * from emp where state=1 and ename like ?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, "%" + ename + "%");
        rs = ps.executeQuery();
        while (rs.next()) {
            Emp emp = new Emp();
            emp.setEmpNo(rs.getInt(1));
            emp.setEname(rs.getString(2));
            emp.setJob(rs.getString("job"));
            emp.setMgr(rs.getInt(4));
            emp.setHireDate(rs.getString(5));
            emp.setSal(rs.getDouble(6));
            emp.setComm(rs.getDouble("comm"));
            emp.setDeptNo(rs.getInt(8));
            emp.setState(rs.getInt(9));
            list.add(emp);
        }
        return list;
    }

    // 分页查询
    @SneakyThrows
    public List<Emp> findByPage(int page) {
        List<Emp> list = new ArrayList<>();
        conn = DBManager.getConnection();
        String sql = "select * from emp where state=1 limit ?,10";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, (page - 1) * 10);
        rs = ps.executeQuery();
        while (rs.next()) {
            Emp emp = new Emp();
            emp.setEmpNo(rs.getInt(1));
            emp.setEname(rs.getString(2));
            emp.setJob(rs.getString("job"));
            emp.setMgr(rs.getInt(4));
            emp.setHireDate(rs.getString(5));
            emp.setSal(rs.getDouble(6));
            emp.setComm(rs.getDouble("comm"));
            emp.setDeptNo(rs.getInt(8));
            emp.setState(rs.getInt(9));
            list.add(emp);
        }
        return list;
    }

}
